/**
 * 班级列表模块数据操作文件
 */

const { query, queryFirst, pool } = require('./common');

/**
 * 根据教师ID查询第一条数据
 * @param {*} id 
 * @returns 
 */
const getFirstClassByTeacherId = (id) => queryFirst('SELECT * FROM class_list WHERE teacher_id = ? LIMIT 1', [id]);

/**
 * 添加班级
 * @param {*} obj 班级信息
 * @returns 
 */
const addClass = obj => query('INSERT INTO class_list SET ?', obj);

/**
 * 修改班级
 * @param {*} obj 班级信息
 * @returns 
 */
const updateClass = (id, obj) => query('UPDATE class_list SET ? WHERE id = ?', [obj, id]);

/**
 * 分页查询
 * @param {*} obj 分页查询信息
 * @returns 
 */
const getClassList = async obj => {
    const { pageNo, pageSize, grade, teacher_id } = obj;
    const conn = await pool.getConnection();
    try {
        const where = conn.format(`WHERE (IFNULL(?,0) = 0 OR teacher_id = ?) AND (? = 0 OR grade_id = ?)`, [teacher_id, teacher_id, grade, grade]);
        const [[list], [[count]]] = await Promise.all([
            conn.query(`SELECT * FROM(SELECT c.*,t.name teacher_name,d.name grade_name,ROW_NUMBER() OVER(ORDER BY id)num FROM class_list c
            INNER JOIN teachers t on c.teacher_id = t.id
            INNER JOIN dictionary d on c.grade_id = d.id
            ${where}
            ) t
            WHERE t.num > ? AND t.num <= ?`, [(pageNo - 1) * pageSize, pageNo * pageSize]),
            conn.query('SELECT count(1) count from class_list ' + where)
        ]);
        return [list, count.count];
    } catch (error) {
        throw error;
    } finally {
        conn.release();
    }
}

/**
 * 是否存在相同的名字
 * @param {*} name 名字
 * @param {*} id id
 * @returns 
 */
const exists = (name, id) => queryFirst('SELECT 1 n FROM class_list WHERE name = ? AND (? = 0 OR id <> ?)', [name, id, id]);

/**
 * 根据ID查询班级是否存在
 * @param {*} id 
 * @returns 
 */
const existsById = id => queryFirst(`SELECT 1 n FROM class_list WHERE id = ?`, [id]);

/**
 * 获取班级名称
 * @param {*} id 
 * @returns 
 */
const getClassName = id => query(`SELECT id,name from class_list WHERE id in (${id.toString()})`);

module.exports = {
    exists,
    addClass,
    existsById,
    updateClass,
    getClassList,
    getClassName,
    getFirstClassByTeacherId
}